package sui.utils.excel.test;

import java.io.IOException;

import org.apache.poi.EncryptedDocumentException;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;

import sui.utils.excel.ReaderExcelGenerateSQL;

public class RecoExcelToSQL {

	/**
	 * 生成SQL文件路径
	 */
	public static final String SQL_FILE_PATH = "F:\\window\\Desktop\\日常运维\\2017-09-15\\";
	
	/**
	 * Excel文件路径
	 */
	private static final String EXCEL_FILE_PATH = "F:\\window\\Desktop\\日常运维\\2017-09-15\\客户来源修改-20170913.xlsx";
	
	private static final String POSEIOND_RECO_BASE_SQL = "INSERT INTO t_customer_reco (id, reco_id, reco_name, reco_type, create_time, operate_time) VALUES ((SELECT id FROM t_customer_sensitive WHERE customer_code = ${customer_code}), (SELECT id FROM t_customer_sensitive WHERE customer_code = ${reco_code}), (SELECT customer_name FROM t_customer_sensitive WHERE customer_code = ${reco_code}), '2', NOW(), NOW());";
	private static final String POSEIOND_SOURCE_BASE_SQL = "UPDATE t_customer_sensitive SET customer_source = 1, customer_source2 = 6, customer_source3 = NULL, operate_time = NOW() WHERE customer_code = ${customer_code};";
	
	private static final String CRM_RECO_BASE_SQL = "INSERT INTO TCR_CRI_CLIENT_RECO (FID, FCLIENT_ID, FRECOMMEND_ID, FNAME, FRECOMMEND_TYPE, FCREATETIME, FOPERATETIME) VALUES (SYS_GUID(), (SELECT FID FROM TCR_CRI_INDI_CLIENT WHERE FCLIENT_CODE = ${customer_code}), (SELECT FID FROM TCR_CRI_INDI_CLIENT WHERE FCLIENT_CODE = ${reco_code}), (SELECT FCLIENT_NAME FROM TCR_CRI_INDI_CLIENT WHERE FCLIENT_CODE = ${reco_code}), 2, SYSDATE, SYSDATE);";
	private static final String CRM_SOURCE_BASE_SQL = "UPDATE TCR_CRI_INDI_CLIENT SET FCLIENT_SOURCE = 1, FCLIENT_SOURCE2 = 6, FCLIENT_SOURCE3 = NULL, FOPERATETIME = SYSDATE WHERE FCLIENT_CODE = ${customer_code};";
	
	private static final String POSEIDON_HAVE_SOURCE_BASE_SQL ="UPDATE t_customer_sensitive SET customer_source = '2', customer_source2 = NULL, customer_source3 = NULL, operate_time = NOW() WHERE customer_code = ${customer_code};";
	private static final String CRM_HAVE_SOURCE_BASE_SQL ="UPDATE TCR_CRI_INDI_CLIENT SET FCLIENT_SOURCE = 2, FCLIENT_SOURCE2 = NULL, FCLIENT_SOURCE3 = NULL, FOPERATETIME = SYSDATE WHERE FCLIENT_CODE = ${customer_code};";
	
	public static void main(String[] args) throws EncryptedDocumentException, InvalidFormatException, IOException {
		ReaderExcelGenerateSQL.readerExcelGenerateSQL(EXCEL_FILE_PATH, POSEIOND_RECO_BASE_SQL, SQL_FILE_PATH);
		ReaderExcelGenerateSQL.readerExcelGenerateSQL(EXCEL_FILE_PATH, POSEIOND_SOURCE_BASE_SQL, SQL_FILE_PATH);
		
		ReaderExcelGenerateSQL.readerExcelGenerateSQL(EXCEL_FILE_PATH, CRM_RECO_BASE_SQL, SQL_FILE_PATH);
		ReaderExcelGenerateSQL.readerExcelGenerateSQL(EXCEL_FILE_PATH, CRM_SOURCE_BASE_SQL, SQL_FILE_PATH);
		
		ReaderExcelGenerateSQL.readerExcelGenerateSQL(EXCEL_FILE_PATH, 1, 0, 1, POSEIDON_HAVE_SOURCE_BASE_SQL, SQL_FILE_PATH);
		ReaderExcelGenerateSQL.readerExcelGenerateSQL(EXCEL_FILE_PATH, 1, 0, 1, CRM_HAVE_SOURCE_BASE_SQL, SQL_FILE_PATH);
	}

}
